package org.eclipse.epsilon.emc.rdbms.core.persistancemechanism;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.eclipse.epsilon.emc.rdbms.core.customexceptions.CustomException;
import org.eclipse.epsilon.emc.rdbms.core.imodel.DBReflexiveModelElement;
import org.h2.tools.RunScript;

public abstract class RelationalDatabase extends PersistanceMechanism
{
	String    URLConnection;
	String    databaseName;
	String    serverName;
	String    userName;
	String    password;
	String    portNumber;
		
	/**
	* This method gets the database properties, although this is not used in the application it is a useful method when delevloping as we can easily check
	* which database we are connected to. 
    */
	 public void getDbProperties(){
	   java.sql.DatabaseMetaData dm = null;
	   java.sql.ResultSet rs = null;
	      try{
	             connection= this.open();
	             if(connection!=null){
	                  dm = connection.getMetaData();
	                  System.out.println("Driver Information");
	                  System.out.println("\tDriver Name: "+ dm.getDriverName());
	                  System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
	                  System.out.println("\nDatabase Information ");
	                  System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
	                  System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
	                  System.out.println("Avalilable Catalogs ");
	                  rs = dm.getCatalogs();
	                  while(rs.next()){
	                       System.out.println("\tcatalog: "+ rs.getString(1));
	                  } 
	                  rs.close();
	                  rs = null;
	                  close();
	             }
	             else new CustomException("Error: There was an issue getting the database property information.");
	        }catch(Exception e){
	             e.printStackTrace();
	        }
	        dm=null;
	   }     

	    /**
		* This method is used to run a specific file from the file system. This will be typically used in database updates or when creating JUnit Test cases to reset the database
		* to a consistent state.
		* @param fileLocation This is the location of the file to be executed.
		*/
	  public void db_setup(String fileLocation)
	   {
		   connection= this.getConnection();
          if(connection!=null)
          {
       	   try 
       	   {
				RunScript.execute(connection, new FileReader(fileLocation));
			   } 
       	   catch (FileNotFoundException e) 
       	   {
				e.printStackTrace();
			   } 
       	   catch (SQLException e) 
       	   {
				e.printStackTrace();
			   }	   
	       }
	   }

	  /**
		* This method gets a list if SQL tables
		* @param tablename This is the tablename that is checked if null is specified then it will return a list of all tables else it will return a list of 
		* the tablename if it exists in the database.
		* 		
		* */ 
	  public abstract ResultSet getSQLTables(String tablename); 
	  	  
	    /**
		* This method is used to retrieve all the SQL Column information from a specific table.
		* @param tablename This is the name of the table to retrieve the column information from.
		*/
	  public abstract ResultSet getSQLColumns(String tablename);
	  
	 /**
	* This method is used to retrieve all the PrimaryKey Column information from a specific table.
	* @param tablename This is the name of the table to retrieve the Primary Key column information from.
	*/	  
	 public abstract ResultSet getPrimaryKey(String tablename);
	  /** This method is used to check the foreign key relationships between tables.
		* @param foreignElement  This is the element that we wish to check if it has any table relationships
		* @return  The resultset of the key relationships for the specified element.
		*/
        public abstract ResultSet getExportedKeys(DBReflexiveModelElement foreignElement) throws SQLException;	
        
        

	  /** This method is used to check the foreign key relationships between tables.
	* @param element  This is the element that we wish to check if it has any table relationships
	* @return  The resultset of the key relationships for the specified element.
	*/
       public abstract ResultSet  getImportedKeys(String element) throws SQLException;
	  
	  
	  /** This method is used to print out a resultset and is used primarily for development purposes and is not used in the Driver. 
	   * @param header  This is the desctiption for the output
	   * @param rs This is the result set we wish to print.
	   * 
	  */
	  //For test purposes Only not needed in final version of the code.
      public static void printResultSet(String header, ResultSet rs) 
      {System.out.println("### ROW_SET_HEADER: "+header+" ###");         
      Integer colWidth = 32;         
      try {             
   	   String headerRow = "";             
   	   String delimiterRow = "";             
   	   ResultSetMetaData rsMetaData = rs.getMetaData();             
   	   for (int i = 1; i <= rsMetaData.getColumnCount(); i++)
   	   {                 
   		   headerRow += String.format("%" + colWidth + "s|", rsMetaData.getColumnName(i));
   		   for (int j = 0; j < colWidth; j++) 
   		   {                     
   			   delimiterRow += "-";                 
   			   }                 
   		   delimiterRow += "|";             
   		   }             
   	   System.out.println(headerRow);             
   	   System.out.println(delimiterRow);             
   	   while (rs.next()) 
   	   {                 
   		   String row = "";                 
   		   for (int i = 1; i <= rsMetaData.getColumnCount(); i++) 
   		   {                     
   			   row += String.format("%" + colWidth + "s|", rs.getString(i));                 
   			   }                 
   		   System.out.println(row);             
   		   }         
   	   } 
         catch (SQLException ex) 
         {   
         } 
      } 

	  
	  
    /** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
    * @return  The relevant SQL command to be added to the statement.
   */	  
public abstract String 	getBeginTransaction();
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public abstract String 	getCommitTransaction();
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public abstract String 	getRollbackTransaction();
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  	 
public String 	getClauseStringAnd()
 {
	
	return " AND ";	 
 }
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringAscend()
{
	return " ASC ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringDescend()
{
	return " DESC ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  	 
public String 	getClauseStringBetween()
{
	return " BETWEEN ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  	 
public String getClauseStringDelete()
{
	return "DELETE FROM ";
	
}

/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String getClauseStringEqualTo()	 
{
	return " = ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String getClauseStringForUpdate()
{
	return "UPDATE ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  	 
public String getClauseStringFrom()
{
	return " FROM ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  	 
public String getClauseStringGroupBy()
{
	return " GROUP BY " ;
	
}

/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String getClauseStringHaving()
{
	return " HAVING ";
	
}


/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  	 
public String 	getClauseStringIn()
{
	return " IN ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String 	getClauseStringInsert()
{
	return "INSERT INTO ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String 	getClauseStringIsNull()
{
	return " IS NULL ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String 	getClauseStringIsNotNull()
{
	return " IS NOT NULL ";
	
}

/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  	 
public String 	getClauseStringLike()
{
	return " LIKE ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringLimit()
{
	return " TOP ";	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringNotLike()
{
	return " NOT LIKE ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringOr()
{
	return " OR ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringOrderBy()
{
	return " ORDER BY ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringSelect()
{
	return "SELECT ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringSet()
{
	return " SET ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringUpdate()
{
	return "UPDATE ";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String   getClauseStringValues()
{
	return " VALUES";
	
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseStringWhere()
{
	return " WHERE ";
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseOpenBrackets()
{
	return " (";
}
/** This method is used to check the return the generic SQL clauses that are used in the database statement builder.
 * @return  The relevant SQL command to be added to the statement.
*/	  
public String	getClauseCloseBrackets()
{
	return ") ";
}
}